package bastanteo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import bastanteo.modelo.Cliente;
import bastanteo.modelo.GrupoBastanteo;
import bastanteo.dao.BaseDAO;
import bastanteo.excepcion.DAOExcepcion;
import bastanteo.util.ConexionBD;

public class ClienteDAO extends BaseDAO{
	
	public void insertar(Cliente clie) throws DAOExcepcion {
		System.out.println("ClienteDAO: insertar(Cliente clie)");
		String query = "INSERT INTO cliente(codClie, ruc,razSoc, fecInicOper, tipoEmp,cantEmp) VALUES (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, clie.getCodClie());
			stmt.setString(2, clie.getRuc());
			stmt.setString(3, clie.getRazSoc());
			stmt.setString(4, clie.getFecInicOper());
			stmt.setString(5, clie.getTipoEmp());
			stmt.setInt(6, clie.getCantEmp());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			else{
				System.out.println("Cliente ingresado correctamente");
			}
				
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public void eliminar(String codClie) throws DAOExcepcion {
		System.out.println("ClienteDAO: eliminar(String codClie)");
		String query = "DELETE FROM cliente WHERE codclie=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, codClie);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public void actualizar(Cliente clie) throws DAOExcepcion {
		System.out.println("ClienteDAO: actualizar(Cliente clie)");
		String query = "update cliente set ruc=? ,razSoc=? , fecInicOper=?, tipoEmp=? , cantEmp=? where codClie=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, clie.getRuc());
			stmt.setString(2, clie.getRazSoc());
			stmt.setString(3, clie.getFecInicOper());
			stmt.setString(4, clie.getTipoEmp());			
			stmt.setInt(5, clie.getCantEmp());
			stmt.setString(6, clie.getCodClie());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
			else{
				System.err.println("Actualizacion correcta");
			}
				
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public Cliente obtener(String codClie) throws DAOExcepcion {
		System.out.println("ClienteDAO: obtener(String codClie)");
		Cliente cli = new Cliente();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "SELECT codClie, ruc,razSoc, fecInicOper, tipoEmp,cantEmp from cliente where codClie=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, codClie);
			rs = stmt.executeQuery();
			if (rs.next()) {
				cli.setCodClie(rs.getString("codClie"));
				cli.setRuc(rs.getString("ruc"));
				cli.setRazSoc(rs.getString("razSoc"));
				cli.setFecInicOper(rs.getString("fecInicOper"));
				cli.setTipoEmp(rs.getString("tipoEmp"));
				cli.setCantEmp(rs.getInt("cantEmp"));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return cli;
	}

	public Collection<Cliente> listar() throws DAOExcepcion {
		System.out.println("ClienteDAO: listar()");
		Collection<Cliente> c = new ArrayList<Cliente>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT codClie, ruc,razSoc, fecInicOper, tipoEmp , cantEmp from cliente";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cliente cli = new Cliente();
				cli.setCodClie(rs.getString("codClie"));
				cli.setRuc(rs.getString("ruc"));
				cli.setRazSoc(rs.getString("razSoc"));
				cli.setFecInicOper(rs.getString("fecInicOper"));
				cli.setTipoEmp(rs.getString("tipoEmp"));				
				c.add(cli);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}
	
	public Collection<Cliente> buscarPorRazSoc(String razSoc) throws DAOExcepcion {
		System.out.println("ClienteDAO: buscarPorNombre razSoc="+razSoc);
		String query = "SELECT codClie, ruc,razSoc, fecInicOper, tipoEmp  FROM cliente WHERE razSoc like ?";
		Collection<Cliente> lista = new ArrayList<Cliente>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + razSoc + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cliente cli = new Cliente();
				cli.setCodClie(rs.getString("codClie"));
				cli.setRuc(rs.getString("ruc"));
				cli.setRazSoc(rs.getString("razSoc"));
				cli.setFecInicOper(rs.getString("fecInicOper"));
				cli.setTipoEmp(rs.getString("tipoEmp"));	
				lista.add(cli);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return lista;
	}

	public Collection<GrupoBastanteo> ListarGruposporCliente(String codClie) throws DAOExcepcion {
		System.out.println("ClienteDAO: ListarGrupos(String codClie)");
		String query = "SELECT codGrupo , nombre  FROM grupoBastanteo WHERE codClie= ?";
		Collection<GrupoBastanteo> lista = new ArrayList<GrupoBastanteo>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "codClie");
			rs = stmt.executeQuery();
			while (rs.next()) {
				GrupoBastanteo grupo = new GrupoBastanteo();
				grupo.setCodGrupo(rs.getString("codGrupo"));
				grupo.setNombre(rs.getString("nombre"));	
				lista.add(grupo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return lista;
	}
	
public int validarCliente(String ruc, String codigo) throws DAOExcepcion {		
		
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int existe=0;
		try {
			String query =  "SELECT 1  from cliente where ruc= ? or codclie=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, ruc);
			stmt.setString(2, codigo);
			rs = stmt.executeQuery();
			if (rs.next()) {
				existe=1;
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return existe;
	}

}
